﻿using WeighingRecord.Models;
using Dapper;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using MySql.Data.MySqlClient;
using System.Collections;
using System;
using static WeighingRecord.ViewModels.WeightAccountViewModel;
using NPOI.Util;

namespace WeighingRecord.Services
{
    public class SQLDataAccess
    {
        //log
        private static readonly log4net.ILog log = LogHelper.GetLogger();
        private static readonly string db = ((AppSettingsSection)ConfigurationManager.OpenExeConfiguration("衡七管家.exe").GetSection("appSettings")).Settings["DBSelect"].Value;

        #region UserModel

        public static UserModel LoadUser(string loginId)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from User where Valid = 1 and LoginId = '{0}'", loginId);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<UserModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static List<UserModel> LoadActiveUser()
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<UserModel>("select * from User where Valid = 1;", new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region RoleModel

        public static string LoadLoginRolePermission(string userId)
        {
            IDbConnection cnn;
            string sql = string.Format("select Role.RolePermission from Role left join User on Role.RoleName = User.UserRole where user.LoginId = '{0}';", userId);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<string>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        #endregion

        #region GoodsModel

        public static List<GoodsModel> LoadActiveGoods(int flag = 1)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            string queryStr = "select * from Goods ";
            if (flag == 1)
            {
                queryStr += "where Valid = 1";
            }
            else if (flag == 0)
            {
                queryStr += "where Valid = 0";
            }
            queryStr += "; ";

            var output = cnn.Query<GoodsModel>(queryStr, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region CustomerModel

        public static List<CustomerModel> LoadActiveCustomer(int flag = 1)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            string queryStr = "select * from Customer ";
            if (flag == 1)
            {
                queryStr += "where Valid = 1";
            }
            else if (flag == 0)
            {
                queryStr += "where Valid = 0";
            }
            queryStr += "; ";

            var output = cnn.Query<CustomerModel>(queryStr, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region CarModel
        public static CarModel LoadCar(string PlateNo)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from Car where Valid = 1 and PlateNo = '{0}';", PlateNo);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static List<CarModel> LoadActiveCar(int flag = 1)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            string queryStr = "select * from Car ";
            if (flag == 1)
            {
                queryStr += "where Valid = 1";
            }
            else if (flag == 0)
            {
                queryStr += "where Valid = 0";
            }
            queryStr += "; ";
            var output = cnn.Query<CarModel>(queryStr, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region WeighingRecordModel

        public static WeighingRecordModel LoadWeighingRecord(string Bh)
        {
            IDbConnection cnn;
            string sql = string.Format("select Bh from WeighingRecord where Bh like '{0}%' order by Bh desc limit 1;", Bh);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static WeighingRecordModel GetByBH(string Bh)
        {
            IDbConnection cnn;
            string sql = string.Format("select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord where Bh = '{0}' order by Bh desc limit 1;", Bh);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static List<WeighingRecordModel> GetWeighingRecords(string ch)
        {
            IDbConnection cnn;
            string sql = string.Format("select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord where Ch = '{0}' AND IsFinish = 1 order by Bh desc limit 100;", ch);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static List<WeighingRecordModel> LoadUnfinishedWeighingRecord(string Kh, string Wz, string Ch, string StartDate, string EndDate,
            string by1, string by2, string by3, string by4, string by5,
             string by6, string by7, string by8, string by9, string by10,
            string by11, string by12, string by13, string by14, string by15,
            string by16, string by17, string by18, string by19, string by20,
            string kh2, string kh3,
            string Mz, string Pz, string Jz, string Mzrq, string Pzrq,
            string Jzrq, string Mzsby, string Pzsby, string Kz, string Kl,
            string Sz, string Bz, string Je, string Czje, string Sxed,
            string Gmzl, string Gg, string fhdw)
        {
            IDbConnection cnn;
            string sql = string.Format(
                            @"select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord 
                where Ch like '{0}' 
                and ifnull(Kh,0) like '{1}' 
                and ifnull(Wz,0) like '{2}' 
                and EntryTime >= '{3}' 
                and EntryTime <= '{4}' 
                and ifnull(by1,0) like '{5}' 
                and ifnull(by2,0) like '{6}' 
                and ifnull(by3,0) like '{7}' 
                and ifnull(by4,0) like '{8}' 
                and ifnull(by5,0) like '{9}' 
                and ifnull(kh2,0) like '{10}' 
                and ifnull(kh3,0) like '{11}' 
                and ifnull(by6,0) like '{12}' 
                and ifnull(by7,0) like '{13}' 
                and ifnull(by8,0) like '{14}' 
                and ifnull(by9,0) like '{15}' 
                and ifnull(by10,0) like '{16}' 
                and ifnull(by11,0) like '{17}' 
                and ifnull(by12,0) like '{18}' 
                and ifnull(by13,0) like '{19}' 
                and ifnull(by14,0) like '{20}' 
                and ifnull(by15,0) like '{21}' 
                and ifnull(by16,0) like '{22}' 
                and ifnull(by17,0) like '{23}' 
                and ifnull(by18,0) like '{24}' 
                and ifnull(by19,0) like '{25}' 
                and ifnull(by20,0) like '{26}' 
                 and ifnull(pz,0) like '{27}' 
                 and ifnull(jz,0) like '{28}' 
                 and ifnull(mzrq,0) like '{29}' 
                 and ifnull(pzrq,0) like '{30}' 
                 and ifnull(jzrq,0) like '{31}' 
                 and ifnull(mzsby,0) like '{32}' 
                 and ifnull(pzsby,0) like '{33}' 
                 and ifnull(kz,0) like '{34}' 
                 and ifnull(kl,0) like '{35}' 
                 and ifnull(sz,0) like '{36}' 
                 and ifnull(bz,0) like '{37}' 
                 and ifnull(je,0) like '{38}' 
                 and ifnull(amount,0) like '{39}' 
                 and ifnull(credit,0) like '{40}' 
                 and ifnull(weigh,0) like '{41}' 
                 and ifnull(mz,0) like '{42}' 
                and ifnull(goodsspec,0) like '{43}' 
                and ifnull(fhdw,0) like '{44}' 
                and IsFinish = 0
                order by AutoNo desc;"
                            , Ch, Kh, Wz, StartDate, EndDate,
                            by1, by2, by3, by4, by5, kh2, kh3,
                             by6, by7, by8, by9, by10, by11, by12, by13, by14, by15, by16,
                         by17, by18, by19, by20, Pz, Jz, Mzrq, Pzrq, Jzrq, Mzsby, Pzsby, Kz, Kl,
                         Sz, Bz, Je, Czje, Sxed, Gmzl, Mz, Gg, fhdw);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static List<WeighingRecordModel> LoadWeighingRecord(
            string Kh, string Wz, string Ch, string StartDate, string EndDate,
            string by1, string by2, string by3, string by4, string by5,
            string by6, string by7, string by8, string by9, string by10,
            string by11, string by12, string by13, string by14, string by15,
            string by16, string by17, string by18, string by19, string by20,
            string kh2, string kh3,
            string Mz, string Pz, string Jz, string Mzrq, string Pzrq,
            string Jzrq, string Mzsby, string Pzsby, string Kz, string Kl,
            string Sz, string Bz, string Je, string Czje, string Sxed,
            string Gmzl, string Gg, string fhdw, int? finishFlag = 1, bool? isUpload = null, decimal maxAbnormalData = 100000m)
        {
            IDbConnection cnn;

            var mzMaxAbnormalCondition = db == "mysql" ? $" and ifnull(CAST(mz as decimal(18,2)),0) <= {maxAbnormalData} " : $" and ifnull(CAST(mz as real),0) <= {maxAbnormalData} ";

            var finishCondition = string.Empty;
            if (finishFlag == 1)
            {
                finishCondition = " and IsFinish = 1 ";
            }
            else if (finishFlag == 0)
            {
                finishCondition = " and IsFinish = 0 ";
            }

            var isUploadCondition = string.Empty;

            if (isUpload != null)
            {
                isUploadCondition = isUpload.GetValueOrDefault() ? " and IsUpload = 1 " : " and IsUpload = 0 ";
            }

            var by20Condition = string.Empty;
            if (string.IsNullOrWhiteSpace(by20))
            {
                by20Condition = $" and (by20 is null or by20 <> '1') ";
            }
            else
            {
                by20Condition = $" and by20 = '{by20}' ";
            }


            string sql = string.Format(
                         @"select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,GoodsPrice,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord 
                where Ch like '{0}' 
                and ifnull(kh,0) like '{1}' 
                and ifnull(Wz,0) like '{2}' 
                and EntryTime >= '{3}' 
                and EntryTime <= '{4}' 
                and ifnull(by1,0) like '{5}' 
                and ifnull(by2,0) like '{6}' 
                and ifnull(by3,0) like '{7}' 
                and ifnull(by4,0) like '{8}' 
                and ifnull(by5,0) like '{9}' 
                and ifnull(kh2,0) like '{10}' 
                and ifnull(kh3,0) like '{11}' 
                and ifnull(by6,0) like '{12}' 
                and ifnull(by7,0) like '{13}' 
                and ifnull(by8,0) like '{14}' 
                and ifnull(by9,0) like '{15}' 
                and ifnull(by10,0) like '{16}' 
                and ifnull(by11,0) like '{17}' 
                and ifnull(by12,0) like '{18}' 
                and ifnull(by13,0) like '{19}' 
                and ifnull(by14,0) like '{20}' 
                and ifnull(by15,0) like '{21}' 
                and ifnull(by16,0) like '{22}' 
                and ifnull(by17,0) like '{23}' 
                and ifnull(by18,0) like '{24}' 
                and ifnull(by19,0) like '{25}' "
                + by20Condition +
                @"and ifnull(pz,0) like '{27}' 
                and ifnull(jz,0) like '{28}' 
                and ifnull(mzrq,0) like '{29}' 
                and ifnull(pzrq,0) like '{30}' 
                and ifnull(jzrq,0) like '{31}' 
                and ifnull(mzsby,0) like '{32}' 
                and ifnull(pzsby,0) like '{33}' 
                and ifnull(kz,0) like '{34}' 
                and ifnull(kl,0) like '{35}' 
                and ifnull(sz,0) like '{36}' 
                and ifnull(bz,0) like '{37}' 
                and ifnull(je,0) like '{38}' 
                and ifnull(amount,0) like '{39}' 
                and ifnull(credit,0) like '{40}' 
                and ifnull(weigh,0) like '{41}' 
                and ifnull(mz,0) like '{42}' 
                and ifnull(goodsspec,0) like '{43}' 
                and ifnull(fhdw,0) like '{44}' "
                + finishCondition + isUploadCondition + mzMaxAbnormalCondition + " order by AutoNo desc;"
                         , Ch, Kh, Wz, StartDate, EndDate, by1, by2, by3, by4, by5, kh2, kh3,
                         by6, by7, by8, by9, by10, by11, by12, by13, by14, by15, by16,
                         by17, by18, by19, by20, Pz, Jz, Mzrq, Pzrq, Jzrq, Mzsby, Pzsby, Kz, Kl,
                         Sz, Bz, Je, Czje, Sxed, Gmzl, Mz, Gg, fhdw);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static List<WeighingRecordModel> LoadWeighingRecordByUploadFlag(bool? isUpload = null)
        {
            IDbConnection cnn;


            var isUploadCondition = string.Empty;

            if (isUpload != null)
            {
                isUploadCondition = isUpload.GetValueOrDefault()
                    ? " and IsUpload = 1 " : " and IsUpload = 0 ";
            }

            string sql = string.Format(
                         @"select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord where valid = 1 "
                 + isUploadCondition + "order by AutoNo desc;");

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }


        public static List<WeighingRecordModel> LoadWeighingRecord(string searchContext, string StartDate, string EndDate, int IsFinish = 1)
        {
            IDbConnection cnn;
            string sql = string.Format(
                     @"select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord 
                where (EntryTime >= '{1}'and  EntryTime <= '{2}')
                 and(  Ch like '%{0}%' 
                 or  ifnull(bh,0) like '%{0}%' 
                 or  ifnull(gblx,0) like '%{0}%' 
                 or  ifnull(kh,0) like '%{0}%' 
                 or  ifnull(Wz,0) like '%{0}%'   
                 or  ifnull(by1,0) like '%{0}%' 
                 or  ifnull(by2,0) like '%{0}%' 
                 or  ifnull(by3,0) like '%{0}%' 
                 or  ifnull(by4,0) like '%{0}%' 
                 or  ifnull(by5,0) like '%{0}%' 
                 or  ifnull(kh2,0) like '%{0}%' 
                 or  ifnull(kh3,0) like '%{0}%' 
                 or  ifnull(by6,0) like '%{0}%' 
                 or  ifnull(by7,0) like '%{0}%' 
                 or  ifnull(by8,0) like '%{0}%' 
                 or  ifnull(by9,0) like '%{0}%' 
                 or  ifnull(by10,0) like '%{0}%' 
                 or  ifnull(by11,0) like '%{0}%' 
                 or  ifnull(by12,0) like '%{0}%' 
                 or  ifnull(by13,0) like '%{0}%' 
                 or  ifnull(by14,0) like '%{0}%' 
                 or  ifnull(by15,0) like '%{0}%' 
                 or  ifnull(by16,0) like '%{0}%' 
                 or  ifnull(by17,0) like '%{0}%' 
                 or  ifnull(by18,0) like '%{0}%' 
                 or  ifnull(by19,0) like '%{0}%' 
                 or  ifnull(by20,0) like '%{0}%' 
                  or  ifnull(pz,0) like '%{0}%' 
                  or  ifnull(jz,0) like '%{0}%' 
                  or  ifnull(mzrq,0) like '%{0}%' 
                  or  ifnull(pzrq,0) like '%{0}%' 
                  or  ifnull(jzrq,0) like '%{0}%' 
                  or  ifnull(mzsby,0) like '%{0}%' 
                  or  ifnull(pzsby,0) like '%{0}%' 
                  or  ifnull(kz,0) like '%{0}%' 
                  or  ifnull(kl,0) like '%{0}%' 
                  or  ifnull(sz,0) like '%{0}%' 
                  or  ifnull(bz,0) like '%{0}%' 
                  or  ifnull(je,0) like '%{0}%' 
                  or  ifnull(amount,0) like '%{0}%' 
                  or  ifnull(credit,0) like '%{0}%' 
                  or  ifnull(weigh,0) like '%{0}%' 
                  or  ifnull(ICCard,0) like '%{0}%'
                  or  ifnull(WeighName,0) like '%{0}%'
                  or  ifnull(Weigh2Name,0) like '%{0}%'
                  or  ifnull(LimitType,0) like '%{0}%'
                  or  ifnull(LimitedValue,0) like '%{0}%'
                  or  ifnull(RiskFactor,0) like '%{0}%'
                  or  ifnull(HostName,0) like '%{0}%'
                  or  ifnull(GoodsSpec,0) like '%{0}%'
                  or  ifnull(Fhdw,0) like '%{0}%'
                  or  ifnull(Driver,0) like '%{0}%'
                  or  ifnull(DriverPhone,0) like '%{0}%'
                  or  ifnull(SerialNumber,0) like '%{0}%'
                  or  ifnull(Zflsh,0) like '%{0}%'
                  or  ifnull(Zfsj,0) like '%{0}%'
                  or  ifnull(WeighingFormTemplate,0) like '%{0}%'
                  or  ifnull(mz,0) like '%{0}%' ) and IsFinish = {3} 
                order by AutoNo desc;", searchContext, StartDate, EndDate, IsFinish);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }



        public static List<WeighingRecordModel> LoadWeighingAccount(string Kh, string Wz, string Ch, string StartDate, string EndDate, string by1, string by2, string by3, string by4, string by5, string kh2, string kh3)
        {
            IDbConnection cnn;
            string sql = string.Format(
                      @"select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord 
                where Valid = 1 and Ch like '{0}' 
                and ifnull(kh,0) like '{1}' 
                and ifnull(Wz,0) like '{2}' 
                and EntryTime >= '{3}' 
                and EntryTime <= '{4}' 
                and ifnull(by1,0) like '{5}' 
                and ifnull(by2,0) like '{6}' 
                and ifnull(by3,0) like '{7}' 
                and ifnull(by4,0) like '{8}' 
                and ifnull(by5,0) like '{9}' 
                and ifnull(kh2,0) like '{10}' 
                and ifnull(kh3,0) like '{11}' 
                order by AutoNo desc;"
                      , Ch, Kh, Wz, StartDate, EndDate, by1, by2, by3, by4, by5, kh2, kh3);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static List<WeighingRecordSumModel> LoadWeighingAccountGroup(string start,
            string end,
            IEnumerable<GroupFieldItem> checkedFields, decimal maxAbnormalData = 100000m)
        {
            IDbConnection cnn;
            var isMysql = db == "mysql";
            var groupFileds = checkedFields.Select(p => p.Column).ToArray();

            var whereList = new List<string>();

            foreach (var field in checkedFields)
            {
                if (field.FilterChecked && !string.IsNullOrWhiteSpace(field.SearchText))
                {
                    whereList.Add($" ifnull({field.Column},0) like '%{field.SearchText}%' ");
                }
            }

            var whereStr = whereList.Count > 0 ? $"and {string.Join(" and ", whereList)}" : string.Empty;

            var mzMaxAbnormalCondition = isMysql ? $" ifnull(CAST(mz as decimal(18,2)),0) <= {maxAbnormalData} " : $" ifnull(CAST(mz as real),0) <= {maxAbnormalData} ";

            string sql = string.Format(
                      @"select {3},sum(cast(mz as decimal(18,2))) as mz,sum(cast(pz as decimal(18,2))) as pz,sum(cast(jz as decimal(18,2))) as jz,sum(cast(sz as decimal(18,2))) as sz,sum(cast(je as decimal(18,2))) as je,sum(cast(goodsprice as decimal(18,2))) as goodsprice,count(ch) as count from (select * from WeighingRecord 
                where Valid = 1 and isfinish = 1 and  (by20 is null or by20 <> 1) and "+ mzMaxAbnormalCondition + @"
                and EntryTime >= '{0}' 
                and EntryTime <= '{1}' {2}) a group by {3};",
                      start, end, whereStr, string.Join(",", groupFileds));

            if (!isMysql)
            {
                sql = sql.Replace("as decimal", "as double");
            }

            if (isMysql)
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordSumModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }


        public static List<WeighingRecordModel> LoadWeighingAccount1(
            string Kh, string Wz, string Ch, string StartDate, string EndDate,
            string by1, string by2, string by3, string by4, string by5,
            string by6, string by7, string by8, string by9, string by10,
            string by11, string by12, string by13, string by14, string by15,
            string by16, string by17, string by18, string by19, string by20,
            string kh2, string kh3)
        {
            IDbConnection cnn;
            string sql = string.Format(
                      @"select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from WeighingRecord 
                where Valid = 1 and Ch like '{0}' 
                and ifnull(Kh,0) like '{1}' 
                and ifnull(Wz,0) like '{2}' 
                and Jzrq >= '{3}' 
                and Jzrq <= '{4}' 
                and ifnull(by1,0) like '{5}' 
                and ifnull(by2,0) like '{6}' 
                and ifnull(by3,0) like '{7}' 
                and ifnull(by4,0) like '{8}' 
                and ifnull(by5,0) like '{9}' 
                and ifnull(kh2,0) like '{10}' 
                and ifnull(kh3,0) like '{11}' 
                and ifnull(by6,0) like '{12}' 
                and ifnull(by7,0) like '{13}' 
                and ifnull(by8,0) like '{14}' 
                and ifnull(by9,0) like '{15}' 
                and ifnull(by10,0) like '{16}' 
                and ifnull(by11,0) like '{17}' 
                and ifnull(by12,0) like '{18}' 
                and ifnull(by13,0) like '{19}' 
                and ifnull(by14,0) like '{20}' 
                and ifnull(by15,0) like '{21}' 
                and ifnull(by16,0) like '{22}' 
                and ifnull(by17,0) like '{23}' 
                and ifnull(by18,0) like '{24}' 
                and ifnull(by19,0) like '{25}' 
                and ifnull(by20,0) like '{26}' 
                order by AutoNo desc;"
                      , Ch, Kh, Wz, StartDate, EndDate, by1, by2, by3, by4, by5, kh2, kh3,
                       by6, by7, by8, by9, by10, by11, by12, by13, by14, by15, by16,
                         by17, by18, by19, by20);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        //public static void SaveWeighingRecord(WeighingRecordModel weighingRecord)
        //{
        //    IDbConnection cnn;

        //    if (db == "mysql")
        //        cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
        //    else
        //        cnn = new SQLiteConnection(LoadConnectionString());

        //    cnn.Execute("INSERT INTO WeighingRecord (Bh, Kh, Kh2, Kh3, Ch, Wz, Mz, Mzrq, Mzsby, Pz, Pzrq, Pzsby, Jz, Jzrq, Kz, Kl, Bz, ICCard, By1, By2, By3, By4, By5, WeighingTimes, IsFinish) " +
        //      "VALUES (@Bh, @Kh, @Kh2, @Kh3, @Ch, @Wz, @Mz, @Mzrq, @Mzsby, @Pz, @Pzrq, @Pzsby, @Jz, @Mzrq, @Kz, @Kl, @Bz, @ICCard, @By1, @By2, @By3, @By4, @By5, @WeighingTimes, @IsFinish);", weighingRecord);

        //    cnn.Close();

        //    log.Info("手动增加称重记录:" + weighingRecord.Bh);
        //}

        public static void SaveWeighingRecord(WeighingRecordModel WeighingRecord)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            try
            {
                cnn.Execute("INSERT INTO WeighingRecord (Bh, Kh, Kh2, Kh3, Ch, Wz, Mz, Mzrq, Mzsby, Pz, Pzrq, Pzsby, Jz, Jzrq, Kz, Kl, Sz, Bz, ICCard, By1, By2, By3, By4, By5,By6,By7,By8,By9,By10,By11,By12,By13,By14,By15,By16,By17,By18,By19,By20, WeighName, Weigh2Name,  WeighingTimes, WeighingFormTemplate, IsFinish, Je,EntryTime,Gblx,AxleNum,IsLimit,LimitedValue,IsUpload,IsRisk,RiskFactor,LimitType,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,IsCover) " +
                    "VALUES (@Bh, @Kh, @Kh2, @Kh3, @Ch, @Wz, @Mz, @Mzrq, @Mzsby, @Pz, @Pzrq, @Pzsby, @Jz, @Jzrq, @Kz, @Kl, @Sz, @Bz, @ICCard, @By1, @By2, @By3, @By4, @By5,@By6,@By7,@By8,@By9,@By10,@By11,@By12,@By13,@By14,@By15,@By16,@By17,@By18,@By19,@By20, @WeighName, @Weigh2Name, @WeighingTimes, @WeighingFormTemplate, @IsFinish, @Je,@EntryTime,@Gblx,@AxleNum,@IsLimit,@LimitedValue,@IsUpload,@IsRisk,@RiskFactor,@LimitType,@GoodsSpec,@Fhdw,@Driver,@DriverPhone,@SerialNumber,@IsCover);", WeighingRecord);


                log.Info("手动增加称重记录:" + WeighingRecord.Bh);
            }
            catch (Exception e)
            {
                if (e.Message.ToLower().Contains("duplicate entry"))
                {
                    if (WeighingRecord.Bh.StartsWith("X"))
                    {
                        WeighingRecord.Bh = $"X{WeighingRecord.Bh}";
                    }
                    else
                    {
                        var appSettings = ((AppSettingsSection)ConfigurationManager.OpenExeConfiguration("衡七管家.exe").GetSection("appSettings")).Settings;

                        var prefix = appSettings["Prefix"]?.Value ?? "D";

                        var type = appSettings["GenerationType"]?.Value ?? "按当日递增";

                        WeighingRecord.Bh = Common.Data.SQLDataAccess.CreateBh(DateTime.Now, prefix, type);
                    }

                    SaveWeighingRecord(WeighingRecord);
                }
            }
            finally
            {
                cnn.Close();
            }

        }



        //public static void UpdateWeighingRecord(WeighingRecordModel weighingRecord)
        //{
        //    IDbConnection cnn;
        //    string sql =
        //           @"UPDATE WeighingRecord SET 
        //        Kh = @Kh, Kh2 = @Kh2, Kh3 = @Kh3, Ch = @Ch, Wz = @Wz,
        //        Mz = @Mz, Mzrq = @Mzrq, Mzsby = @Mzsby, 
        //        Pz = @Pz, Pzrq = @Pzrq, Pzsby = @Pzsby, 
        //        Jz = @Jz, Jzrq = @Mzrq, Kz = @kz, Kl = @Kl, Sz = @Sz, Bz = @Bz, ICCard = @ICCard,
        //        By1 = @By1, By2 = @By2, By3 = @By3, By4 = @By4, By5 = @By5,
        //        WeighingTimes = @WeighingTimes, IsFinish = @IsFinish 
        //        WHERE Bh = @Bh;";

        //    if (db == "mysql")
        //        cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
        //    else
        //        cnn = new SQLiteConnection(LoadConnectionString());

        //    cnn.Execute(sql, weighingRecord);

        //    cnn.Close();

        //    log.Info("修改称重记录:" + weighingRecord.Bh);
        //}

        public static void UpdateWeighingRecord(WeighingRecordModel WeighingRecord)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("UPDATE WeighingRecord SET Ch = @Ch,Kh = @Kh, Kh2 = @Kh2, Kh3 = @Kh3, Wz = @Wz, Mz = @Mz, Mzrq = @Mzrq, Mzsby = @Mzsby, Pz = @Pz, Pzrq = @Pzrq, Pzsby = @Pzsby, Jz = @Jz, Jzrq = @Jzrq, Kz = @kz, Kl = @Kl, Sz = @Sz, Bz = @Bz, ICCard = @ICCard, By1 = @By1, By2 = @By2, By3 = @By3, By4 = @By4, By5 = @By5,By6 = @By6,By7 = @By7,By8 = @By8,By9 = @By9,By10 = @By10,By11 = @By11,By12 = @By2,By13 = @By13,By14 = @By14,By15 = @By15,By16 = @By16,By17 = @By17,By18 = @By18,By19 = @By19,By20 = @By20, WeighName = @WeighName, Weigh2Name = @Weigh2Name, WeighingTimes = @WeighingTimes, WeighingFormTemplate = @WeighingFormTemplate, IsFinish = @IsFinish, Je = @Je,EntryTime=@EntryTime,Gblx=@Gblx,AxleNum=@AxleNum,IsLimit=@IsLimit,LimitedValue=@LimitedValue,IsUpload=@IsUpload,IsRisk=@IsRisk,RiskFactor=@RiskFactor,LimitType=@LimitType,GoodsSpec=@GoodsSpec,Fhdw=@Fhdw,Driver=@Driver,DriverPhone=@DriverPhone,SerialNumber=@SerialNumber,IsCover=@IsCover WHERE Bh = @Bh;", WeighingRecord);

            cnn.Close();

            log.Info("修改称重记录:" + WeighingRecord.Bh);
        }

        public static void DeleteWeighingRecord(WeighingRecordModel weighingRecord)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("UPDATE WeighingRecord SET Valid = 0 WHERE Bh = @Bh;", weighingRecord);

            cnn.Close();

            log.Info("删除称重记录:" + weighingRecord.Bh);
        }


        public static void ResetWeighingRecord(WeighingRecordModel weighingRecord)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("UPDATE WeighingRecord SET Valid = 1 WHERE Bh = @Bh;", weighingRecord);

            cnn.Close();

            log.Info("删除称重记录:" + weighingRecord.Bh);
        }
        #endregion

        #region 备用字段
        public static List<string> LoadByxValue(string byName)
        {
            IDbConnection cnn;
            //string sql = string.Format("select distinct {0} from WeighingRecord where valid = 1 order by {0};", byName);
            string sql = string.Format("select distinct {0} from WeighingRecord where 1 = 1 order by {0};", byName);
            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<string>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region SyncDataModel
        public static void SaveSyncData(SyncDataModel SyncData)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO SyncData (WeighingRecordBh, SyncMode) VALUES (@WeighingRecordBh, @SyncMode);", SyncData);

            cnn.Close();
        }
        public static List<WeighingRecordModel> LoadWeighingRecordCount(string plateno, string date)
        {
            IDbConnection cnn;
            string sql = $"select AutoNo,Bh,Kh,Kh2,Kh3,Ch,Wz,Mz,Mzrq,Mzsby, Pz,Pzrq,Pzsby,Jz,Jzrq,Kz,Kl,Sz,Bz,ICCard,By1,By2,By3,By4,By5,WeighingTimes,WeighingFormTemplate,IsFinish,Valid,WeighName,Weigh2Name,Je,EntryTime,Gblx, By6,By7,By8,By9,By10,By11,By12,By13,By14, By15,By16,By17,By18, By19,By20,IsPay, Zflsh,Zfsj,Zfje,Zfdw,Zfdzsm,AxleNum,IsLimit,LimitType,LimitedValue,IsUpload,IsRisk,RiskFactor,HostName,Amount,Credit,Weigh,GoodsSpec,Fhdw,Driver,DriverPhone,SerialNumber,case when IsCover is null then 0 else IsCover  end as  IsCover from weighingrecord where Ch = '{plateno}' and IsFinish = '1' and Jzrq like '{date}%';";

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }
        #endregion

        #region 获取物资列表

        public static List<GoodsModel> LoadWz()
        {
            IDbConnection cnn;
            string sql = $"select * from goods where valid =  1;";

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<GoodsModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region 获取物资规格列表

        public static List<Common.Models.GoodsSpecModel> LoadGoodsSpec()
        {
            IDbConnection cnn;
            string sql = $"select * from goodsspec;";

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<Common.Models.GoodsSpecModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region Overload
        public static void SavOverloadLog(OverloadLog SyncData, string flag)
        {
            if (flag != "1") return;//不储存超载日志。1、存储，0、不储存

            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO OverloadLog (PlateNo, Constraints,AverageWeight,OverloadWeight,StandardWeight,AxleCount,Times,CreateDate) VALUES (@PlateNo, @Constraints,@AverageWeight,@OverloadWeight,@StandardWeight,@AxleCount,@Times,@CreateDate);", SyncData);

            cnn.Close();
        }
        #endregion

        private static string LoadConnectionString(string id = "AWSDB") => ConfigurationManager.ConnectionStrings[id].ConnectionString;
    }
}
